'''

Case Type： 索引文件
Case Name： 索引文件损坏
Create At:  2020/07/01
Owner    :  n@ningyali
Descption:  1.建表建索引并插入大量数据 2.查询索引的oid 3.通过oid查询索引文件的位置 4.删除部分索引文件内容 5.重启数据库以清除缓存 6.查看执行计划是否使用索引 7.使用索引查询表数据 8.重建索引

'''

import os
import sys
import time
import unittest
from yat.test import Node
from yat.test import macro

sys.path.append(sys.path[0] + "/../")
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant
from testcase.utils.Common import Common
from testcase.utils.CommonSH import *


class IndexFileDamaged(unittest.TestCase):
    sh = CommonSH()
    nodes_tuple = ('PrimaryDbUser', 'Standby1DbUser', 'Standby2DbUser')

    @RestartDbCluster(*nodes_tuple)
    def setUp(self):
        self.log = Logger()
        self.log.info('------------Opengauss_Reliability_Dbsys_Case066.py start------------')
        self.DN1_PATH = macro.DB_INSTANCE_PATH
        self.dbUserNode = Node(node='dbuser')
        self.com = Common()
        self.Constant = Constant()
        self.t_name = 't_test'
        self.t_index_name = 't_test_index'

    def test_Index_file_damaged(self):
        # 新建表
        self.log.info('----------------------------创建表，并写入数据-----------------------------')
        msg = self.com.createLargeNumTable(self.dbUserNode, self.t_name, 1, 1000000)
        self.log.info(msg)

        # 建索引
        sql_cmd = f"create unique index {self.t_index_name} on {self.t_name}(t_id);"
        self.log.info(sql_cmd)
        msg = self.sh.executDbSql(sql_cmd)
        self.log.info(msg)

        # 查询索引oid
        self.log.info('----------------------------查询索引oid-----------------------------')
        sql_cmd = f"select oid from pg_class where relname='{self.t_index_name}';"
        self.log.info(sql_cmd)
        msg = self.sh.executDbSql(sql_cmd)
        self.log.info(msg)
        oid = msg.splitlines()[-2].strip()

        # 通过oid查询索引文件位置
        self.log.info('----------------------------通过oid查询索引文件位置-----------------------------')
        sql_cmd = f"select pg_relation_filepath({oid}::regclass);"
        self.log.info(sql_cmd)
        msg = self.sh.executDbSql(sql_cmd)
        self.log.info(msg)
        path = msg.splitlines()[-2].strip()
        self.log.info(path)

        # 删除部分索引文件内容(删除第一行以外的内容)
        self.log.info(f'----------------------------删除部分索引文件内容-----------------------------')
        index_file_path = os.path.join(macro.DB_INSTANCE_PATH, path)
        self.log.info(index_file_path)

        command = r"$d"
        cmd = f"sed -i '1,{command}' {index_file_path}"
        self.log.info(cmd)
        msg = self.dbUserNode.sh(cmd).result()
        self.assertEqual(msg, '')

        self.log.info(f'----------------------------删除文件内容后查看文件信息-----------------------------')
        cmd = f"ls -lh {index_file_path}"
        self.log.info(cmd)
        msg = self.dbUserNode.sh(cmd).result()
        self.log.info(msg)

        # 重启以清除缓存
        self.log.info(f'----------------------------重启以清除缓存-----------------------------')
        is_stop = self.sh.stopDbCluster()
        self.assertTrue(is_stop)

        self.log.info(f'----------------------------停止数据库后查看文件信息-----------------------------')
        cmd = f"ls -lh {index_file_path}"
        self.log.info(cmd)
        msg = self.dbUserNode.sh(cmd).result()
        self.log.info(msg)

        time.sleep(2)

        is_started = self.sh.startDbCluster()
        self.assertTrue(is_started)

        self.log.info(f'----------------------------启动数据库后查看文件信息-----------------------------')
        cmd = f"ls -lh {index_file_path}"
        self.log.info(cmd)
        msg = self.dbUserNode.sh(cmd).result()
        self.log.info(msg)

        # 查看执行计划，以确定使用索引查询
        self.log.info('----------------------------查看执行计划，以确定使用索引查询-----------------------------')
        sql_cmd = f'explain select * from {self.t_name} where t_id = 999;'
        self.log.info(sql_cmd)
        msg = self.sh.executDbSql(sql_cmd)
        self.log.info(msg)
        self.assertIn('Index Scan', msg)

        # 使用索引字段查询
        self.log.info('----------------------------使用索引字段查询-----------------------------')
        sql_cmd = f'select * from {self.t_name} where t_id = 999;'
        self.log.info(sql_cmd)
        msg = self.sh.executDbSql(sql_cmd)
        self.log.info(msg)
        self.assertIn(self.Constant.SQL_WRONG_MSG[1], msg)

        # 重建索引以还原
        self.log.info('----------------------------重建索引以还原-----------------------------')
        sql_cmd = f'reindex index {self.t_index_name};'
        self.log.info(sql_cmd)
        msg = self.sh.executDbSql(sql_cmd)
        self.log.info(msg)
        self.assertIn('REINDEX', msg)

        time.sleep(3)

        # 使用索引字段查询
        self.log.info('----------------------------使用索引字段查询-----------------------------')
        sql_cmd = f'select * from {self.t_name} where t_id = 999;'
        self.log.info(sql_cmd)
        msg = self.sh.executDbSql(sql_cmd)
        self.log.info(msg)
        self.assertNotIn(self.Constant.SQL_WRONG_MSG[1], msg)

    def tearDown(self):
        # 清理环境，删除表
        msg = self.com.createLargeNumTable(self.dbUserNode, self.t_name, del_sign=True)
        self.log.info(msg)
        self.log.info('----------------Opengauss_Reliability_Dbsys_Case066:用例执行完毕--------------------')
